﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'PRC_REPORT_TASK_HISTORY_GENERAL_SE')
	BEGIN
		DROP  Procedure  dbo.PRC_REPORT_TASK_HISTORY_GENERAL_SE
	END

GO

CREATE Procedure dbo.PRC_REPORT_TASK_HISTORY_GENERAL_SE
	(
		 @projects xml
		,@responsibles xml
		,@startDateFrom datetime
		,@startDateTo datetime
		,@endDateFrom datetime
		,@endDateTo datetime
	)
AS

select
	tu_name,
	tuh_project,
	isnull(tuhc_description,'Não classificado') as tuhc_description,
	tuh_start_date,
	tuh_end_date,
	tuh_hours_day
from TB_USER_TASK_HISTORY
	inner join tb_user
		on tuh_user_id = tu_id
	left join TB_USER_TASK_HISTORY_CLASSIFICATION
		on tuh_classification_id = tuhc_id
where TUH_TYPE = 1
and (@projects is null or tuh_project in (select RTRIM(LTRIM(ParamValues.project_id.value('.','VARCHAR(150)'))) from @projects.nodes('/ProjectSearch/Projects/project_id') as ParamValues(project_id)))
and (@responsibles is null or tu_id in (select RTRIM(LTRIM(ParamValues.user_ident.value('.','int'))) from @responsibles.nodes('/UserSearch/Users/user_ident') as ParamValues(user_ident)))
and (@startDateFrom is null or datediff(dd,@startDateFrom,tuh_start_date) >= 0)
and (@startDateTo is null or datediff(dd,@startDateTo,tuh_start_date) <= 0)
and (@endDateFrom is null or datediff(dd,@endDateFrom,tuh_end_date) >= 0)
and (@endDateTo is null or datediff(dd,@endDateTo,tuh_end_date) <= 0)
order by tuh_project ,tu_name

GO

GRANT EXEC ON dbo.PRC_REPORT_TASK_HISTORY_GENERAL_SE TO PUBLIC

GO

